
use rusqlite::{ params, Transaction, Connection };
use serde::{ Serialize, Deserialize };
use std::{ default::Default };
use super::db;


#[derive(Serialize, Deserialize, Debug, Default)]
#[serde(default)]
struct Company {
    id: u32,
    name: String,
    orders: u32,
}

#[derive(Serialize, Deserialize, Debug, Default)]
#[serde(default)]
struct Project {
    id: u32,
    name: String,
    #[serde(rename = "cId")]
    cid: u32,
    orders: u32
}

#[derive(Serialize, Deserialize, Debug, Default)]
#[serde(default)]
struct Item {
    id: u32,
    name: String,
    url: Option<String>,
    content: Option<String>,
    #[serde(rename = "pId")]
    pid: u32,
    orders: u32
}

#[derive(Serialize, Deserialize, Debug, Default)]
#[serde(default)]
struct Bookmark {
    id: u32,
    #[serde(rename = "parentId")]
    parent_id: u32,
    #[serde(rename = "type")]
    _type: u32,
    title: String,
    url: String,
    icon: Option<String>,
    orders: u32,
    hidden: u32
}

#[derive(Serialize, Deserialize, Debug, Default)]
#[serde(default)]
struct User {
    id: u32,
    username: Option<String>,
    password: Option<String>,
    #[serde(rename = "secretKey")]
    secret_key: String,
}


/******************************************************************************************
 * Company Apis
 */
#[tauri::command]
pub fn get_company_all() -> Result<String, String> {
    let conn = db::get_connect();

    let mut stmt = match conn.prepare_cached("select id, name, orders from tb_company order by orders") {
        Ok(stmt) => stmt,
        Err(err) => {
            return Err(err.to_string());
        }
    };

    let query = stmt.query_map([], |row| {
        Ok(Company {
            id: row.get(0)?,
            name: row.get(1)?,
            orders: row.get(2)?,
        })
    });

    let entity_iter = match query {
        Ok(entity_iter) => entity_iter,
        Err(err) => {
            return Err(err.to_string());
        }
    };

    let mut entity_vec: Vec<Company> = vec![];
    for entity in entity_iter {
        entity_vec.push(entity.unwrap());
    }

    match serde_json::to_string(&entity_vec) {
        Ok(result) => Ok(result),
        Err(err) => Err(err.to_string())
    }
}

#[tauri::command]
pub fn get_company(payload: u32) -> Result<String, String> {
    let conn = db::get_connect();

    let mut stmt = match conn.prepare_cached("select id, name, orders from tb_company where id = ?") {
        Ok(stmt) => stmt,
        Err(err) => {
            return Err(err.to_string());
        }
    };
    
    let query = stmt.query_map([payload], |row| {
        Ok(Company {
            id: row.get(0)?,
            name: row.get(1)?,
            orders: row.get(2)?,
        })
    });

    let entity_iter = match query {
        Ok(entity_iter) => entity_iter,
        Err(err) => {
            return Err(err.to_string());
        }
    };

    let mut entity_vec: Vec<Company> = vec![];
    for entity in entity_iter {
        entity_vec.push(entity.unwrap());
    }

    match serde_json::to_string(&entity_vec) {
        Ok(json) => Ok(json),
        Err(err) => Err(err.to_string())
    }
}


#[tauri::command]
pub fn add_company(payload: String) -> Result<i64, String> {
    let conn = db::get_connect();

    let company = match serde_json::from_str::<Company>(&payload) {
        Ok(company) => company,
        Err(err) => {
            return Err(err.to_string());
        }
    };

    let query = conn.execute(
        "insert into tb_company (name, orders) values (?1, ?2)", 
        params![&company.name, company.orders]
    );

    match query {
        Ok(_) => Ok(conn.last_insert_rowid()),
        Err(err) => Err(err.to_string())
    }
}


#[tauri::command]
pub fn delete_company(payload: u32) -> Result<(), String> {
    let mut conn = db::get_connect();

    let tx = match conn.transaction() {
        Ok(tx) => tx,
        Err(err) => return Err(err.to_string()),
    };

    delete_item_by_cid_tx(&tx, payload)?;
    delete_project_by_cid_tx(&tx, payload)?;

    let query = tx.execute(
        "delete from tb_company where id = ?1", 
        params![payload]
    );

    match tx.commit() {
        Ok(_) => (),
        Err(err) => return Err(err.to_string()),
    };

    match query {
        Ok(_) => Ok(()),
        Err(err) => Err(err.to_string())
    }
}

#[tauri::command]
pub fn update_company(payload: String) -> Result<usize, String> {
    let conn = db::get_connect();

    let Ok(company) = serde_json::from_str::<Company>(&payload) else {
        return Err("Invalid json".to_string());
    };

    match conn.execute(
        "update tb_company set name = ?, orders = ? where id = ?", 
        params![&company.name, company.orders, company.id]
    ) {
        Ok(count) => Ok(count),
        Err(err) => Err(err.to_string())
    }
}




/******************************************************************************************
 * Project Apis
 */
#[tauri::command]
pub fn get_project_all() -> Result<String, String> {
    let conn = db::get_connect();

    let mut stmt = match conn.prepare_cached("select id, name, orders, cid  from tb_project order by orders") {
        Ok(stmt) => stmt,
        Err(err) => {
            return Err(err.to_string());
        }
    };

    let query = stmt.query_map([], |row| {
        Ok(Project {
            id: row.get(0)?,
            name: row.get(1)?,
            orders: row.get(2)?,
            cid: row.get(3)?,
        })
    });

    let entity_iter = match query {
        Ok(entity_iter) => entity_iter,
        Err(err) => {
            return Err(err.to_string());
        }
    };

    let mut entity_vec: Vec<Project> = vec![];
    for entity in entity_iter {
        entity_vec.push(entity.unwrap());
    }

    match serde_json::to_string(&entity_vec) {
        Ok(result) => Ok(result),
        Err(err) => Err(err.to_string())
    }
}

#[tauri::command]
pub fn get_project(payload: u32) -> Result<String, String> {
    let conn = db::get_connect();

    let mut stmt = match conn.prepare_cached("select id, name, orders, cid from tb_project where id = ?") {
        Ok(stmt) => stmt,
        Err(err) => {
            return Err(err.to_string());
        }
    };
    
    let query = stmt.query_map([payload], |row| {
        Ok(Project {
            id: row.get(0)?,
            name: row.get(1)?,
            orders: row.get(2)?,
            cid: row.get(3)?,
        })
    });

    let entity_iter = match query {
        Ok(entity_iter) => entity_iter,
        Err(err) => {
            return Err(err.to_string());
        }
    };

    let mut entity_vec: Vec<Project> = vec![];
    for entity in entity_iter {
        entity_vec.push(entity.unwrap());
    }

    match serde_json::to_string(&entity_vec) {
        Ok(json) => Ok(json),
        Err(err) => Err(err.to_string())
    }
}


#[tauri::command]
pub fn add_project(payload: String) -> Result<i64, String> {
    let conn = db::get_connect();

    let entity = match serde_json::from_str::<Project>(&payload) {
        Ok(company) => company,
        Err(err) => {
            return Err(err.to_string());
        }
    };

    let query = conn.execute(
        "insert into tb_project (name, orders, cid) values (?1, ?2, ?3)", 
        params![&entity.name, entity.orders, entity.cid]
    );

    match query {
        Ok(_) => Ok(conn.last_insert_rowid()),
        Err(err) => Err(err.to_string())
    }
}


#[tauri::command]
pub fn delete_project(payload: u32) -> Result<(), String> {
    let mut conn = db::get_connect();

    let tx = match conn.transaction() {
        Ok(tx) => tx,
        Err(err) => return Err(err.to_string()),
    };

    delete_item_by_pid_tx(&tx, payload)?;

    let query = tx.execute(
        "delete from tb_project where id = ?1", 
        params![payload]
    );

    match tx.commit() {
        Ok(_) => (),
        Err(err) => return Err(err.to_string()),
    }

    match query {
        Ok(_) => Ok(()),
        Err(err) => Err(err.to_string())
    }
}

#[tauri::command]
pub fn delete_project_by_cid(payload: u32) -> Result<(), String> {
    let conn = db::get_connect();

    let query = conn.execute(
        "delete from tb_project where cid = ?1", 
        params![payload]
    );

    match query {
        Ok(_) => Ok(()),
        Err(err) => Err(err.to_string())
    }
}

pub fn delete_project_by_cid_tx(tx: &Transaction, payload: u32) -> Result<(), String> {
    let query = tx.execute(
        "delete from tb_project where cid = ?1", 
        params![payload]
    );

    match query {
        Ok(_) => Ok(()),
        Err(err) => Err(err.to_string())
    }
}

#[tauri::command]
pub fn update_project(payload: String) -> Result<usize, String> {
    let conn = db::get_connect();

    let Ok(entity) = serde_json::from_str::<Project>(&payload) else {
        return Err("Invalid json".to_string());
    };

    match conn.execute(
        "update tb_project set name = ?, orders = ?, cid = ? where id = ?", 
        params![&entity.name, entity.orders, entity.cid, entity.id]
    ) {
        Ok(count) => Ok(count),
        Err(err) => Err(err.to_string())
    }
}




/******************************************************************************************
 * Item Apis
 */
#[tauri::command]
pub fn get_item_all() -> Result<String, String> {
    let conn = db::get_connect();

    let mut stmt = match conn.prepare_cached("select id, name, orders, pid, url, content from tb_item order by orders") {
        Ok(stmt) => stmt,
        Err(err) => {
            return Err(err.to_string());
        }
    };

    let query = stmt.query_map([], |row| {
        Ok(Item {
            id: row.get(0)?,
            name: row.get(1)?,
            orders: row.get(2)?,
            pid: row.get(3)?,
            url: row.get(4)?,
            content: row.get(5)?,
        })
    });

    let entity_iter = match query {
        Ok(entity_iter) => entity_iter,
        Err(err) => {
            return Err(err.to_string());
        }
    };

    let mut entity_vec: Vec<Item> = vec![];
    for entity in entity_iter {
        entity_vec.push(entity.unwrap());
    }

    match serde_json::to_string(&entity_vec) {
        Ok(result) => Ok(result),
        Err(err) => Err(err.to_string())
    }
}

#[tauri::command]
pub fn get_item(payload: u32) -> Result<String, String> {
    let conn = db::get_connect();

    let mut stmt = match conn.prepare_cached("select id, name, orders, pid, url, content from tb_item where id = ?") {
        Ok(stmt) => stmt,
        Err(err) => {
            return Err(err.to_string());
        }
    };
    
    let query = stmt.query_map([payload], |row| {
        Ok(Item {
            id: row.get(0)?,
            name: row.get(1)?,
            orders: row.get(2)?,
            pid: row.get(3)?,
            url: row.get(4)?,
            content: row.get(5)?,
        })
    });

    let entity_iter = match query {
        Ok(entity_iter) => entity_iter,
        Err(err) => {
            return Err(err.to_string());
        }
    };

    let mut entity_vec: Vec<Item> = vec![];
    for entity in entity_iter {
        entity_vec.push(entity.unwrap());
    }

    match serde_json::to_string(&entity_vec) {
        Ok(json) => Ok(json),
        Err(err) => Err(err.to_string())
    }
}


#[tauri::command]
pub fn add_item(payload: String) -> Result<i64, String> {
    let conn = db::get_connect();

    let entity = match serde_json::from_str::<Item>(&payload) {
        Ok(entity) => entity,
        Err(err) => {
            return Err(err.to_string());
        }
    };

    let query = conn.execute(
        "insert into tb_item (name, orders, pid, url, content) values (?1, ?2, ?3, ?4, ?5)", 
        params![&entity.name, entity.orders, entity.pid, entity.url, &entity.content]
    );

    match query {
        Ok(_) => Ok(conn.last_insert_rowid()),
        Err(err) => Err(err.to_string())
    }
}


#[tauri::command]
pub fn delete_item(payload: u32) -> Result<(), String> {
    let conn = db::get_connect();

    let query = conn.execute(
        "delete from tb_item where id = ?1", 
        params![payload]
    );

    match query {
        Ok(_) => Ok(()),
        Err(err) => Err(err.to_string())
    }
}

pub fn delete_item_by_pid(payload: u32) -> Result<(), String> {
    let conn = db::get_connect();

    let query = conn.execute(
        "delete from tb_item where pid = ?1", 
        params![payload]
    );

    match query {
        Ok(_) => Ok(()),
        Err(err) => Err(err.to_string())
    }
}

pub fn delete_item_by_pid_tx(tx: &Transaction, payload: u32) -> Result<(), String> {
    let query = tx.execute(
        "delete from tb_item where pid = ?1", 
        params![payload]
    );

    match query {
        Ok(_) => Ok(()),
        Err(err) => Err(err.to_string())
    }
}

pub fn delete_item_by_cid_tx(tx: &Transaction, payload: u32) -> Result<(), String> {
    let query = tx.execute(
        "delete from tb_item where pid in (select id from tb_project where cid = ?)", 
        params![payload]
    );

    match query {
        Ok(_) => Ok(()),
        Err(err) => Err(err.to_string())
    }
}

#[tauri::command]
pub fn update_item(payload: String) -> Result<usize, String> {
    let conn = db::get_connect();

    let Ok(entity) = serde_json::from_str::<Item>(&payload) else {
        return Err("Invalid json".to_string());
    };

    match conn.execute(
        "update tb_item set name = ?, orders = ?, pid = ?, url = ?, content = ? where id = ?", 
        params![&entity.name, entity.orders, entity.pid, entity.url, entity.content, entity.id]
    ) {
        Ok(count) => Ok(count),
        Err(err) => Err(err.to_string())
    }
}



/******************************************************************************************
 * Bookmark Apis
 */
#[tauri::command]
pub fn get_bookmark_all() -> Result<String, String> {
    let conn = db::get_connect();

    let sql = "select id, parent_id, type, title, url, icon, orders, hidden from tb_bookmark";
    let mut stmt = match conn.prepare_cached(&sql) {
        Ok(stmt) => stmt,
        Err(err) => {
            return Err(err.to_string());
        }
    };

    let query = stmt.query_map([], |row| {
        Ok(Bookmark {
            id: row.get(0)?,
            parent_id: row.get(1)?,
            _type: row.get(2)?,
            title: row.get(3)?,
            url: row.get(4)?,
            icon: row.get(5)?,
            orders: row.get(6)?,
            hidden: row.get(7)?,
        })
    });

    let entity_iter = match query {
        Ok(entity_iter) => entity_iter,
        Err(err) => {
            return Err(err.to_string());
        }
    };

    let mut entity_vec: Vec<Bookmark> = vec![];
    for entity in entity_iter {
        entity_vec.push(entity.unwrap());
    }

    match serde_json::to_string(&entity_vec) {
        Ok(result) => Ok(result),
        Err(err) => Err(err.to_string())
    }
}

#[tauri::command]
pub fn get_bookmark(payload: u32) -> Result<String, String> {
    let conn = db::get_connect();

    let sql = "select id, parent_id, type, title, url, icon, orders, hidden from tb_bookmark where id = ?";
    let mut stmt = match conn.prepare_cached(&sql) {
        Ok(stmt) => stmt,
        Err(err) => {
            return Err(err.to_string());
        }
    };
    
    let query = stmt.query_map([payload], |row| {
        Ok(Bookmark {
            id: row.get(0)?,
            parent_id: row.get(1)?,
            _type: row.get(2)?,
            title: row.get(3)?,
            url: row.get(4)?,
            icon: row.get(5)?,
            orders: row.get(6)?,
            hidden: row.get(7)?,
        })
    });

    let entity_iter = match query {
        Ok(entity_iter) => entity_iter,
        Err(err) => {
            return Err(err.to_string());
        }
    };

    let mut entity_vec: Vec<Bookmark> = vec![];
    for entity in entity_iter {
        entity_vec.push(entity.unwrap());
    }

    match serde_json::to_string(&entity_vec) {
        Ok(json) => Ok(json),
        Err(err) => Err(err.to_string())
    }
}


#[tauri::command]
pub fn add_bookmark(payload: String) -> Result<i64, String> {
    let conn = db::get_connect();

    let entity = match serde_json::from_str::<Bookmark>(&payload) {
        Ok(entity) => entity,
        Err(err) => {
            return Err(err.to_string());
        }
    };

    let sql = "insert into tb_bookmark (parent_id, type, title, url, icon, orders, hidden) values (?,?,?,?,?,?,?)";
    let query = conn.execute(&sql, 
        params![&entity.parent_id, &entity._type, &entity.title, &entity.url, &entity.icon, &entity.orders, &entity.hidden]
    );

    match query {
        Ok(_) => Ok(conn.last_insert_rowid()),
        Err(err) => Err(err.to_string())
    }
}


#[tauri::command]
pub fn delete_bookmark(payload: u32) -> Result<(), String> {
    let conn = db::get_connect();

    let query = conn.execute(
        "delete from tb_bookmark where id = ? or parent_id = ?", 
        params![payload, payload]
    );

    match query {
        Ok(_) => Ok(()),
        Err(err) => Err(err.to_string())
    }
}


#[tauri::command]
pub fn update_bookmark(payload: String) -> Result<usize, String> {
    let conn = db::get_connect();

    let Ok(entity) = serde_json::from_str::<Bookmark>(&payload) else {
        return Err("Invalid json".to_string());
    };

    match conn.execute(
        "update tb_bookmark set parent_id = ?, type = ?, title = ?, url = ?, icon = ?, orders = ?, hidden = ? where id = ?", 
        params![&entity.parent_id, &entity._type, &entity.title, &entity.url, &entity.icon, &entity.orders, &entity.hidden, &entity.id]
    ) {
        Ok(count) => Ok(count),
        Err(err) => Err(err.to_string())
    }
}


/******************************************************************************************
 * User Apis
 */

#[tauri::command]
pub fn get_user() -> Result<String, String> {
    let conn = db::get_connect();

    let sql = "select id, username, password, secret_key from tb_user where id = 1";
    let mut stmt = match conn.prepare_cached(&sql) {
        Ok(stmt) => stmt,
        Err(err) => {
            return Err(err.to_string());
        }
    };
    
    let query = stmt.query_row([], |row| {
        Ok(User {
            id: row.get(0)?,
            username: row.get(1)?,
            password: row.get(2)?,
            secret_key: row.get(3)?,
        })
    });

    let entity = match query {
        Ok(entity) => entity,
        Err(_) => {
            User{id: 1, username: None, password: None, secret_key: "".to_string()}
        }
    };

    match serde_json::to_string(&entity) {
        Ok(json) => Ok(json),
        Err(err) => Err(err.to_string())
    }
}


#[tauri::command]
pub fn add_user(payload: String) -> Result<i64, String> {
    let conn = db::get_connect();

    let entity = match serde_json::from_str::<User>(&payload) {
        Ok(entity) => entity,
        Err(err) => {
            return Err(err.to_string());
        }
    };

    let sql = "insert into tb_user (secret_key) values (?)";
    let query = conn.execute(&sql, 
        params![&entity.secret_key]
    );

    match query {
        Ok(_) => Ok(1),
        Err(err) => Err(err.to_string())
    }
}

#[tauri::command]
pub fn update_user(payload: String) -> Result<usize, String> {
    let conn = db::get_connect();

    let Ok(entity) = serde_json::from_str::<User>(&payload) else {
        return Err("Invalid json".to_string());
    };

    match conn.execute(
        "update tb_user set secret_key = ? where id = 1", 
        params![&entity.secret_key]
    ) {
        Ok(count) => Ok(count),
        Err(err) => Err(err.to_string())
    }
}

#[tauri::command]
pub fn add_or_update_user(payload: String) -> Result<usize, String> {
    let conn = db::get_connect();

    let Ok(entity) = serde_json::from_str::<User>(&payload) else {
        return Err("Invalid json in user".to_string());
    };

    let get_sql = "select id, username, password, secret_key as secretKey from tb_user where id = 1";
    let exists_user = conn.query_row(&get_sql, [], |row| {
        Ok(User {
            id: row.get(0)?,
            username: row.get(1)?,
            password: row.get(2)?,
            secret_key: row.get(3)?,
        })
    });

    if let Ok(user) = exists_user {
        // update
        match conn.execute(
            "update tb_user set secret_key = ? where id = 1", 
            params![&user.secret_key]
        ) {
            Ok(count) => Ok(count),
            Err(err) => Err(err.to_string())
        }
    } else {
        // insert
        let sql = "insert into tb_user (id, secret_key) values (1, ?)";
        match conn.execute(&sql, params![&entity.secret_key]) {
            Ok(_) => Ok(1),
            Err(err) => Err(err.to_string())
        }
    }
}


/******************************************************************************************
 * Init Table
 */

#[tauri::command]
pub fn init_tables() -> Result<(), String> {
    let company_sql = "CREATE TABLE if not exists tb_company(id integer primary key autoincrement, name varchar not null, orders integer default 10)";
    let project_sql = "CREATE TABLE if not exists tb_project(id integer primary key autoincrement, name varchar not null, cid integer not null, orders integer default 10)";
    let item_sql = "CREATE TABLE if not exists tb_item(id integer primary key autoincrement, name varchar, url varchar, content text, pid integer not null, orders integer default 10)";
    let bookmark_sql = "CREATE TABLE if not exists tb_bookmark(id integer primary key autoincrement, parent_id integer not null default 0, type tinyint not null default 1, title varchar(100), url varchar(1024), icon text,orders tinyint default 10, create_time datetime default current_timestamp, update_time datetime default current_timestamp, hidden tinyint default 1)";
    let user_sql    = "CREATE TABLE if not exists tb_user(id integer primary key autoincrement, username varchar(100), password varchar(1024), secret_key varchar(1024), create_time datetime default current_timestamp, update_time datetime default current_timestamp)";

    let conn = db::get_connect();

    match conn.execute(company_sql, ()) {
        Ok(_) => {}
        Err(e) => return Err(e.to_string()),
    };
    match conn.execute(project_sql, ()) {
        Ok(_) => {}
        Err(e) => return Err(e.to_string()),
    };
    match conn.execute(item_sql, ()) {
        Ok(_) => {}
        Err(e) => return Err(e.to_string()),
    };
    match conn.execute(bookmark_sql, ()) {
        Ok(_) => {}
        Err(e) => return Err(e.to_string()),
    };
    match conn.execute(user_sql, ()) {
        Ok(_) => {}
        Err(e) => return Err(e.to_string()),
    };

    Ok(())
}

fn init_data(conn: &Connection) {
    let init_company_sql = "insert into tb_company (name) values ('公司甲')";
    let init_project_sql = "insert into tb_project (name, cid) values ('工程1', 1)";
    let init_item_sql = "insert into tb_item (name, url, pid) values ('项目1', 'https://www.baidu.com', 1)";

    let _ = conn.execute(init_company_sql, ());
    let _ = conn.execute(init_project_sql, ());
    let _ = conn.execute(init_item_sql, ());
}


/******************************************************************************************
 * Other Apis
 */
use tauri_plugin_opener::OpenerExt;

#[tauri::command]
pub fn open_link(app_handle: tauri::AppHandle, url: &str) -> Result<(), String> {
    match app_handle.opener().open_url(url, None::<&str>) {
        Ok(_) => Ok(()),
        Err(e) => return Err(e.to_string()),
    }
}

#[tauri::command]
pub fn get_config() -> Result<String, String> {
    let con = db::get_config();

    match serde_json::to_string(&con) {
        Ok(json) => Ok(json),
        Err(err) => Err(err.to_string())
    }
}